package com.leadingsoft.pwxk.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class ExportExcel {

	private static final Logger log = LoggerFactory
			.getLogger(ExportExcel.class);

	private HSSFWorkbook wb = null;
	private HSSFSheet sheet = null;
	private Font borderFont = null;
	private Font headFont = null;
	private Font firstHeadFont = null;

	private static final String SONG_TI_FONT = "宋体";

	public ExportExcel(HSSFWorkbook wb, HSSFSheet sheet) {
		super();
		this.wb = wb;
		this.sheet = sheet;
		headFont = wb.createFont();
		borderFont = wb.createFont();
		firstHeadFont = wb.createFont();
	}

	/**
	 * 创建通用EXCEL头部
	 * 
	 * @param headString
	 *            头部显示的字符
	 * @param colSum
	 *            该报表的列数
	 */
	@SuppressWarnings("deprecation")
	public void createNormalHead(String headString, int colSum) {

		HSSFRow row = sheet.createRow(0);

		// 设置第一行
		HSSFCell cell = row.createCell(0);
		row.setHeight((short) 600);

		// 定义单元格为字符串类型
		cell.setCellType(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(new HSSFRichTextString(headString));

		// 指定合并区域
		sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) colSum));

		HSSFCellStyle cellStyle = wb.createCellStyle();

		// 指定单元格居中对齐
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		// 指定单元格自动换行
		cellStyle.setWrapText(true);

		// 设置单元格字体
		HSSFFont borderFont = wb.createFont();
		borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		borderFont.setFontName(SONG_TI_FONT);
		borderFont.setFontHeight((short) 300);
		cellStyle.setFont(borderFont);

		cell.setCellStyle(cellStyle);
	}

	/**
	 * 创建通用报表第二行
	 * 
	 * @param params
	 *            统计条件数组
	 * @param colSum
	 *            需要合并到的列索引
	 */
	@SuppressWarnings("deprecation")
	public void createNormalTwoRow(String[] params, int colSum) {
		HSSFRow row1 = sheet.createRow(1);
		row1.setHeight((short) 400);

		HSSFCell cell2 = row1.createCell(0);

		cell2.setCellType(HSSFCell.ENCODING_UTF_16);
		cell2.setCellValue(new HSSFRichTextString("统计时间：" + params[0] + "至"
				+ params[1]));

		// 指定合并区域
		sheet.addMergedRegion(new CellRangeAddress(1, (short) 0, 1, (short) colSum));

		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
		cellStyle.setWrapText(true);// 指定单元格自动换行

		// 设置单元格字体
		HSSFFont borderFont = wb.createFont();
		borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		borderFont.setFontName(SONG_TI_FONT);
		borderFont.setFontHeight((short) 250);
		cellStyle.setFont(borderFont);
		cell2.setCellStyle(cellStyle);
	}

	/**
	 * 设置标题行
	 * 
	 * @param headString
	 *            标题名称
	 * @param lineNumber
	 *            在哪一行设置标题
	 */
	public void setHeadLine(String[] headString, int lineNumber) {
		headFont.setFontName(SONG_TI_FONT);
		headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		CellStyle style3 = createTrueBorderCellStyle(wb,
				HSSFColor.GREY_25_PERCENT.index,
				HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.ALIGN_CENTER,
				headFont);
		HSSFRow row = sheet.createRow(lineNumber);
		row.setHeight((short) 800);
		for (int i = 0; i < headString.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style3);
			HSSFRichTextString text2 = new HSSFRichTextString(headString[i]);
			cell.setCellValue(text2);
		}
	}
	/**
	 * 设置标题行
	 * 
	 * @param headString
	 *            标题名称
	 * @param lineNumber
	 *            在哪一行设置标题
	 */
	public void setFirstHeadLine(String[] headString, int lineNumber) {
		firstHeadFont.setFontName(SONG_TI_FONT);
		firstHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		firstHeadFont.setFontHeightInPoints((short)22);
		CellStyle style3 = createTrueBorderCellStyle(wb,
				HSSFColor.GREY_25_PERCENT.index,
				HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.ALIGN_CENTER,
				firstHeadFont);
		HSSFRow row = sheet.createRow(lineNumber);
		row.setHeight((short) 1200);
		for (int i = 0; i < headString.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style3);
			HSSFRichTextString text2 = new HSSFRichTextString(headString[i]);
			cell.setCellValue(text2);
		}
	}
	/**
	 * 创建内容单元格
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param row
	 *            HSSFRow
	 * @param col
	 *            short型的列索引
	 * @param align
	 *            对齐方式
	 * @param val
	 *            列值
	 */
	public void createCell(HSSFWorkbook wb, HSSFRow row, int col, short align,
                           String val) {
		borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		CellStyle style = createTrueBorderCellStyle(wb, HSSFColor.WHITE.index,
				HSSFColor.WHITE.index, HSSFCellStyle.ALIGN_CENTER, borderFont);
		HSSFCell cell = row.createCell(col);
		cell.setCellType(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(new HSSFRichTextString(val));
		cell.setCellStyle(style);
	}

	/**
	 * 创建内容单元格
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param row
	 *            HSSFRow
	 * @param col
	 *            short型的列索引
	 * @param align
	 *            对齐方式
	 * @param val
	 *            列值
	 */
	public void taskReportcreateCell(HSSFWorkbook wb, HSSFRow row, int col,
                                     short align, String val, String columnName) {
		borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		CellStyle style = createTrueBorderCellStyle(wb, HSSFColor.WHITE.index,
				HSSFColor.WHITE.index, HSSFCellStyle.ALIGN_CENTER, borderFont);
		HSSFCell cell = row.createCell(col);
		HSSFRow upRow = sheet.getRow(row.getRowNum() - 1);
		if ("序号".equals(columnName)) {
			HSSFCell upCell = upRow.getCell((short) 0);
			upCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			String upCellValue = upCell.getStringCellValue();
			if (!StringUtils.isBlank(val)) {
				if (val.equals(upCellValue)) {
					sheet.addMergedRegion(new CellRangeAddress(upRow.getRowNum(),
							(short) 0, row.getRowNum(), (short) 0));// 指定合并区域
					sheet.addMergedRegion(new CellRangeAddress(upRow.getRowNum(),
							(short) 1, row.getRowNum(), (short) 1));// 指定合并区域
					sheet.addMergedRegion(new CellRangeAddress(upRow.getRowNum(),
							(short) 2, row.getRowNum(), (short) 2));// 指定合并区域
				}
			}
		}

		if ("项目状态".equals(columnName)) {
			// 获取上一行的序号和当前行的序号是否相同
			HSSFCell curcell = row.getCell((short) 0);
			curcell.setCellType(HSSFCell.CELL_TYPE_STRING);
			String curCellValue = curcell.getStringCellValue();

			HSSFCell upCell = upRow.getCell((short) 0);
			upCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			String upCellValue = upCell.getStringCellValue();
			if (!StringUtils.isBlank(curCellValue)) {
				if (curCellValue.equals(upCellValue)) {
					// 获取当前列和上一行的当前列
					HSSFCell upCell3 = upRow.getCell((short) 3);
					upCell3.setCellType(HSSFCell.CELL_TYPE_STRING);
					String upCellValue3 = upCell3.getStringCellValue();
					if (!StringUtils.isBlank(val)) {
						if (val.equals(upCellValue3)) {
							sheet.addMergedRegion(new CellRangeAddress(upRow.getRowNum(),
									(short) 3, row.getRowNum(), (short) 3));// 指定合并区域
							sheet.addMergedRegion(new CellRangeAddress(upRow.getRowNum(),
									(short) 4, row.getRowNum(), (short) 4));// 指定合并区域
						}
					}
				}
			}
		}
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(new HSSFRichTextString(val));
		cell.setCellStyle(style);
	}

	/**
	 * 创建内容单元格
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param row
	 *            HSSFRow
	 * @param col
	 *            short型的列索引
	 * @param align
	 *            对齐方式
	 * @param val
	 *            列值
	 */
	public void createCellForEightFour(HSSFWorkbook wb, HSSFRow row, int col,
                                       short align, String val, String columnName,
                                       short beginMergedRegionNumber, short endMergedRegionNumber) {
		borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		CellStyle style = createTrueBorderCellStyle(wb, HSSFColor.WHITE.index,
				HSSFColor.WHITE.index, HSSFCellStyle.ALIGN_CENTER, borderFont);
		HSSFCell cell = row.createCell(col);
		HSSFRow upRow = sheet.getRow(row.getRowNum() - 1);
		if ("序号".equals(columnName)) {
			sheet.addMergedRegion(new CellRangeAddress(beginMergedRegionNumber,
					(short) 0, endMergedRegionNumber, (short) 0));// 指定合并区域
		} else {
			sheet.addMergedRegion(new CellRangeAddress(beginMergedRegionNumber,
					(short) 1, endMergedRegionNumber, (short) 1));// 指定合并区域
		}
		cell.setCellType(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(new HSSFRichTextString(val));
		cell.setCellStyle(style);
	}

	/**
	 * 创建合计行
	 * 
	 * @param colSum
	 *            需要合并到的列索引
	 * @param cellValue
	 */
	@SuppressWarnings("deprecation")
	public void createLastSumRow(int colSum, String[] cellValue) {

		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
		cellStyle.setWrapText(true);// 指定单元格自动换行

		// 单元格字体
		HSSFFont borderFont = wb.createFont();
		borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		borderFont.setFontName(SONG_TI_FONT);
		borderFont.setFontHeight((short) 250);
		cellStyle.setFont(borderFont);

		HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
		HSSFCell sumCell = lastRow.createCell(0);

		sumCell.setCellValue(new HSSFRichTextString("合计"));
		sumCell.setCellStyle(cellStyle);
		sheet.addMergedRegion(new CellRangeAddress(sheet.getLastRowNum(), (short) 0,
				sheet.getLastRowNum(), (short) colSum));// 指定合并区域

		for (int i = 2; i < (cellValue.length + 2); i++) {
			sumCell = lastRow.createCell(i);
			sumCell.setCellStyle(cellStyle);
			sumCell.setCellValue(new HSSFRichTextString(cellValue[i - 2]));
		}
	}
	

	/**
	 * 输出EXCEL文件
	 * 
	 * @param fileName
	 *            文件名
	 */
	public void outputExcel(String fileName) {
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(new File(fileName));
			wb.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			log.error(new StringBuffer("[").append(e.getMessage()).append("]")
					.append(e.getCause()).toString());
			e.printStackTrace();
		} catch (IOException e) {
			log.error(new StringBuffer("[").append(e.getMessage()).append("]")
					.append(e.getCause()).toString());
			e.printStackTrace();
		} finally {
			try {
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
				log.error(new StringBuffer("[").append(e.getMessage()).append(
						"]").append(e.getCause()).toString());
			}
		}
	}

	/**
	 * 功能：创建带边框的CellStyle样式（实线框）
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param backgroundColor
	 *            背景色
	 * @param foregroundColor
	 *            前置色
	 * @param borderFont
	 *            字体
	 * @return CellStyle
	 */

	public CellStyle createTrueBorderCellStyle(HSSFWorkbook wb,
                                               short backgroundColor, short foregroundColor, short halign,
                                               Font borderFont) {
		CellStyle cs = wb.createCellStyle();
		cs.setAlignment(halign);
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setFillBackgroundColor(backgroundColor);
		cs.setFillForegroundColor(foregroundColor);
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(borderFont);
		cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cs.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		return cs;
	}

	/**
	 * 设置列宽（每列统一宽度）
	 * 
	 * @param sheet
	 * @param width
	 * @return
	 */
	public HSSFSheet setColumnWidthStyle(HSSFSheet sheet, int width) {
		sheet.setDefaultColumnWidth(width);
		return sheet;
	}

	/**
	 * 设置某一列的宽度
	 * 
	 * @param sheet
	 * @param width
	 * @return
	 */
	public HSSFSheet setColumnsWidthStyle(HSSFSheet sheet, int column, int width) {
		sheet.setColumnWidth(column, width);
		return sheet;
	}

	/**
	 * 设置某一列的宽度
	 * 
	 * @param sheet
	 * @param width
	 * @return HSSFSheet
	 */
	public HSSFSheet setColumnsWidthsStyle(HSSFWorkbook wb, HSSFSheet sheet,
                                           List<String> columns, List<String> widths) {
		for (int i = 0; i < columns.size(); i++) {
			wb.createCellStyle();
			sheet.setColumnWidth(Integer.parseInt(columns.get(i)), Integer
					.parseInt(widths.get(i)));
		}
		return sheet;
	}

	public HSSFSheet getSheet() {
		return sheet;
	}

	public void setSheet(HSSFSheet sheet) {
		this.sheet = sheet;
	}

	public HSSFWorkbook getWb() {
		return wb;
	}

	public void setWb(HSSFWorkbook wb) {
		this.wb = wb;
	}

}
